package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Logbook;

public class DBLogbook implements DBLogbookIF
{
	private Connection con;
    
    public DBLogbook()
    {
    	con = DBConnection.getInstance().getDBcon();
    }
	
    public ArrayList<Logbook> getAllLogbook(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    
    public ArrayList<Logbook> getAllLogbooksByTrainedID(int trainedID, boolean retriveAssociation)
    {
    	String wClause = "TrainedID = " + trainedID + "";
        return miscWhere(wClause, retriveAssociation);
    }

    public Logbook searchLogbookID(int logbookID, boolean retriveAssociation)
    {   
    	String wClause = "LogbookID = " + logbookID + "";
        return singleWhere(wClause, retriveAssociation);
    }
    
    public int insertLogbook(Logbook logbook)
    {
        int nextId = GetMax.getMaxId("SELECT MAX(LogbookID) FROM Logbook");
        nextId = nextId + 1;
        System.out.println("next LogbookID = " +  nextId);
        
       int rc = -1;
       String query="SET IDENTITY_INSERT Logbook ON INSERT INTO Logbook(LogbookID, PMDate, Note, TrainedID) VALUES (" +
            nextId  + ",'"  + 
            logbook.getDate()  + "','" + 
            logbook.getNote() + "'," + 
            logbook.getTrainedID() + ") SET IDENTITY_INSERT Logbook OFF";
           
      System.out.println("insert : " + query);                          
      try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(200);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }   
        catch(Exception ex){
            System.out.println("Insert exception in logbook db: "+ex);
       }
       return(rc);
    }
    
    private ArrayList<Logbook> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Logbook> logbookList = new ArrayList<Logbook>();	
		
		String query = buildQuery(wClause);
  
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
			while( results.next() )
			{
				Logbook lObj = new Logbook();
				lObj = buildLogbook(results);
				logbookList.add(lObj);	
			}
			stmt.close();
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return logbookList;
	}
	
	private Logbook singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Logbook lObj = new Logbook();
	    String query = buildQuery(wClause);
		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			lObj = buildLogbook(results);
	 			stmt.close();
			}
		}	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return lObj;
	}

	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Logbook";
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;		
		return query;
	}

	private Logbook buildLogbook(ResultSet results)
      {   
		Logbook lObj = new Logbook();
          try
          {
                lObj.setLogbookID(results.getInt("LogbookID"));
                lObj.setDate(results.getString("PMDate"));
                lObj.setNote(results.getString("Note"));
                lObj.setTrainedID(results.getInt("TrainedID"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Trained object");
         }
         return lObj;
      }
}
